﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DAL;
using DTO;
using System.Collections;
using System.Globalization;

namespace BLL
{
    public class hocsinhbll
    {

        static int id = 0;
        QuanLyDiemDataContext DB = new QuanLyDiemDataContext();
        private string loi = "Khong Loi";
        // Ham in ra loi
        public string inloi()
        {
            return loi;
        }
        public IQueryable Selecthocsinh()
        {
            return DB.HocSinhs.OrderByDescending(s => s.MaHocSinh).AsQueryable();
        }
        public IQueryable Selecthocsinh1(string maphanlop)
        {

            var hs = from h in DB.HocSinhs
                     where h.MaPhanLop == maphanlop
                     select new
                     {
                         MaHocSinh = h.MaHocSinh,
                         MaPhanLop = h.MaPhanLop,
                         HoTen = h.HoTen,
                         GioiTinh = h.NgaySinh,
                         NgaySinh = h.NgaySinh,
                         NoiSinh = h.NoiSinh,
                         TonGiao = h.TonGiao,
                         DanToc = h.DanToc,
                         DiaChi = h.DiaChi,
                         TenCha = h.TenCha,
                         NgheNghiepCha = h.NgheNghiepCha,
                         TenMe = h.NgheNghiepMe,
                         NgheNghiepMe = h.NgheNghiepMe,
                         DienThoai = h.DienThoai,
                         TrangThai = h.TrangThai
                     };
            return hs;

        }
        public IQueryable Selecthocsinh2()
        {

            var hs = from h in DB.HocSinhs

                     select new
                     {
                         MaHocSinh = h.MaHocSinh,
                         MaPhanLop = h.MaPhanLop,
                         HoTen = h.HoTen,
                         GioiTinh = h.NgaySinh,
                         NgaySinh = h.NgaySinh,
                         NoiSinh = h.NoiSinh,
                         TonGiao = h.TonGiao,
                         DanToc = h.DanToc,
                         DiaChi = h.DiaChi,
                         TenCha = h.TenCha,
                         NgheNghiepCha = h.NgheNghiepCha,
                         TenMe = h.NgheNghiepMe,
                         NgheNghiepMe = h.NgheNghiepMe,
                         DienThoai = h.DienThoai,
                         TrangThai = h.TrangThai
                     };
            return hs;

        }
        public string Insert(hocsinhDTO hocsinh)
        {
            try
            {
                HocSinh hs = new HocSinh();
                hs.MaHocSinh = (id++).ToString();
                hs.HoTen = hocsinh.Hoten;
                hs.GioiTinh = hocsinh.Gioitinh;
                hs.NgaySinh = hocsinh.NgaySinh;
                hs.NoiSinh = hocsinh.Noisinh;
                hs.TonGiao = hocsinh.Tongiao;
                hs.DanToc = hocsinh.Dantoc;
                hs.DiaChi = hocsinh.Diachi;
                hs.TenCha = hocsinh.Tencha;
                hs.NgheNghiepCha = hocsinh.Nghenghiepcha;

                hs.TenMe = hocsinh.Tenme;
                hs.NgheNghiepMe = hocsinh.Nghenghiepme;
                hs.DienThoai = hocsinh.Dienthoai;
                hs.TrangThai = hocsinh.Trangthai;
                hs.MaPhanLop = hocsinh.Maphanlop;



                DB.HocSinhs.InsertOnSubmit(hs);
                DB.SubmitChanges();
                return "Thanh cong";

            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }
        public string[] laymanglop()
        {
            string[] data = new string[1000];
            data = (from t in DB.Lops
                    select t.TenLop).ToArray();
            return data;
        }

        public string[] themmangnamhoc()
        {
            string[] data = new string[1000];
            data = (from t in DB.NamHocs
                    where t.TrangThai.Contains("Chưa Kết Thúc")
                    select t.TenNamHoc
                      ).ToArray();
            return data;
        }
        public string Update(hocsinhDTO hocsinh)
        {
            try
            {
                HocSinh hs = DB.HocSinhs.Where(s => s.MaHocSinh == hocsinh.Mahocsinh).FirstOrDefault();
                hs.MaPhanLop = hocsinh.Maphanlop;
                hs.HoTen = hocsinh.Hoten;

                hs.GioiTinh = hocsinh.Gioitinh;
                hs.NgaySinh = hocsinh.NgaySinh;
                hs.NoiSinh = hocsinh.Noisinh;
                hs.TonGiao = hocsinh.Tongiao;
                hs.DanToc = hocsinh.Dantoc;
                hs.DiaChi = hocsinh.Diachi;
                hs.TenCha = hocsinh.Tencha;
                hs.NgheNghiepCha = hocsinh.Nghenghiepcha;


                hs.TenMe = hocsinh.Tenme;
                hs.NgheNghiepMe = hocsinh.Nghenghiepme;
                hs.DienThoai = hocsinh.Dienthoai;
                hs.TrangThai = hocsinh.Trangthai;

                DB.SubmitChanges();
                return "Thanh cong";

            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }
        public string Delete()
        {
            try
            {
                DB.SubmitChanges();
                return "Thanh cong";

            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }
        public List<LopDTO> getLop()
        {
            List<Lop> list = DB.Lops.ToList<Lop>();
            List<LopDTO> listdto = new List<LopDTO>();
            foreach (Lop lop in list)
            {
                LopDTO l = new LopDTO();
                l.Malop = lop.MaLop;
                l.Tenlop= lop.TenLop;
                listdto.Add(l);
            }
            return listdto;
        }
        public string laymalop(string maphanlop)
        {
            QuanLyDiemDataContext DB = new QuanLyDiemDataContext();
            return DB.PhanLops.Where(s => s.MaPhanLop == maphanlop).Select(ma => ma.MaLop).FirstOrDefault();

            //string ma = DB.NhaSanXuats.Where(nsx => nsx.TenNSX == "Xí nghiệp dược phẩm Trung ương 5").Select(n => n.MaNSX).FirstOrDefault().ToString();
            //return ma;
        }
        public string laymalopthem(string tenlopthem)
        {
            QuanLyDiemDataContext DB = new QuanLyDiemDataContext();
            return DB.Lops.Where(s => s.TenLop == tenlopthem).Select(ma => ma.MaLop).FirstOrDefault();

            //string ma = DB.NhaSanXuats.Where(nsx => nsx.TenNSX == "Xí nghiệp dược phẩm Trung ương 5").Select(n => n.MaNSX).FirstOrDefault().ToString();
            //return ma;
        }
        public string laymanamhocthem(string tennamhocthem)
        {
            QuanLyDiemDataContext DB = new QuanLyDiemDataContext();
            return DB.NamHocs.Where(s => s.TenNamHoc == tennamhocthem).Select(ma => ma.MaNamHoc).FirstOrDefault();

            //string ma = DB.NhaSanXuats.Where(nsx => nsx.TenNSX == "Xí nghiệp dược phẩm Trung ương 5").Select(n => n.MaNSX).FirstOrDefault().ToString();
            //return ma;
        }
        public string laymaphanlopthem(string manamhoc, string malop)
        {
            QuanLyDiemDataContext DB = new QuanLyDiemDataContext();
            return DB.PhanLops.Where(s => s.MaNamHoc == manamhoc && s.MaLop == malop).Select(ma => ma.MaPhanLop).FirstOrDefault();

            //string ma = DB.NhaSanXuats.Where(nsx => nsx.TenNSX == "Xí nghiệp dược phẩm Trung ương 5").Select(n => n.MaNSX).FirstOrDefault().ToString();
            //return ma;
        }

        public string laynamhocma(string maphanlop)
        {
            QuanLyDiemDataContext DB = new QuanLyDiemDataContext();
            return DB.PhanLops.Where(s => s.MaPhanLop == maphanlop).Select(ma => ma.MaNamHoc).FirstOrDefault();

            //string ma = DB.NhaSanXuats.Where(nsx => nsx.TenNSX == "Xí nghiệp dược phẩm Trung ương 5").Select(n => n.MaNSX).FirstOrDefault().ToString();
            //return ma;
        }
        public DateTime chuyenkieudatetime(string time)
        {
            DateTime datetime = Convert.ToDateTime("1/1/1990");
            try
            {
                DateTimeFormatInfo dtfi = new DateTimeFormatInfo();
                dtfi.ShortDatePattern = "MM/dd/yyyy";
                dtfi.DateSeparator = "/";
                datetime = Convert.ToDateTime(time, dtfi);
            }
            catch (FormatException e)
            {
                loi = e.Message;
            }
            return datetime;
        }

        public List<NamHocDTO> getNamHoc()
        {
            //lay het du lieu tu bang khoi
            List<NamHoc> list = DB.NamHocs.Where(s => s.TrangThai == "Chưa kết thúc").ToList<NamHoc>();
            //tao moi 1 doi tuong KhoiDTO de chua du lieu cua bang khoi
            List<NamHocDTO> listdto = new List<NamHocDTO>();
            foreach (NamHoc nam in list)
            {
                NamHocDTO k = new NamHocDTO();
                k.MaNamHoc = nam.MaNamHoc;
                k.TenNamHoc = nam.TenNamHoc;
                listdto.Add(k);
            }
            return listdto;
        }
        public string getMaPhanLop(string manamhoc, string malop)
        {
            QuanLyDiemDataContext DB = new QuanLyDiemDataContext();
            return DB.PhanLops.Where(s => s.MaNamHoc == manamhoc && s.MaLop == malop).Select(ma => ma.MaPhanLop).FirstOrDefault();

        }
        //lay danh sach hoc sinh cho cho xem danh sach trong form hocsinh
        public IQueryable LayDanhSach_Hoan(string matkb)
        {
            var kq = (from bd in DB.BangDiems
                      join hs in DB.HocSinhs
                      on bd.MaHocSinh equals hs.MaHocSinh into traves
                      from trave in traves.DefaultIfEmpty()
                      where bd.MaTKB == matkb
                      select new
                      {
                          MaHocSinh = trave.MaHocSinh,
                          MaPhanLop = trave.MaPhanLop,
                          HoTen = trave.HoTen,
                          GioiTinh = trave.GioiTinh,
                          NgaySinh = trave.NgaySinh,
                          NoiSinh = trave.NoiSinh,
                          TonGiao = trave.TonGiao,
                          DanToc = trave.DanToc,
                      }
                ).Distinct();
                return kq;
        }
        public IQueryable LayDanhSach_Hoan2(string malop, string manamhoc)
        {
            var kq = (from phlop in DB.PhanLops
                      join hs in DB.HocSinhs
                      on phlop.MaPhanLop equals hs.MaPhanLop into traves
                      from trave in traves.DefaultIfEmpty()
                      where phlop.MaLop == malop && phlop.MaNamHoc == manamhoc
                      select trave
                     ).Distinct();
            return kq;
                        
        }
        public List<LopDTO> getLopDaPhanLop(string manamhoc)
        {
            //lay het du lieu tu bang khoi
            var list = DB.Lops.Join(
                DB.PhanLops,
                a => a.MaLop,
                a => a.MaLop,
                (a, b) => new
                {
                    a.MaLop,
                    a.TenLop,
                    a.MaKhoi,
                    b.MaNamHoc
                }
                ).Where(s => s.MaNamHoc == manamhoc).AsQueryable();
            //tao moi 1 doi tuong KhoiDTO de chua du lieu cua bang khoi
            List<LopDTO> listdto = new List<LopDTO>();
            foreach (var lop in list)
            {
                LopDTO k = new LopDTO();
                k.Malop = lop.MaLop;
                k.Tenlop = lop.TenLop;
                listdto.Add(k);
            }
            return listdto;
        }

    }
}
